package com.bw.dao;

import com.bw.bean.AA;
import com.bw.bean.AB;

import com.bw.bean.AC;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import org.springframework.web.bind.annotation.RequestMapping;

import java.util.List;

@Mapper
public interface AAdao {

    @Select("SELECT b.name,sum(o.final_total_amount) `value` FROM order_info o \n" +
            "LEFT JOIN  base_province b on o.province_id=b.id\n" +
            "GROUP BY b.name ORDER BY `value` desc")
    List<AA> getlist();

    @Select("SELECT t.name,sum(o.final_total_amount) `value`FROM order_info o \n" +
            "LEFT JOIN order_detail d ON d.order_id=o.id \n" +
            "LEFT JOIN sku_info s ON d.sku_id=s.id \n" +
            "LEFT JOIN base_trademark t ON s.tm_id=t.id \n" +
            "GROUP BY t.name ORDER BY `value` desc")
    List<AA> getlist2();

    @Select("SELECT c1.name,sum(o.final_total_amount) `value`FROM order_info o \n" +
            "LEFT JOIN order_detail d ON d.order_id=o.id \n" +
            "LEFT JOIN sku_info s ON d.sku_id=s.id \n" +
            "LEFT JOIN base_category3 c3 ON s.category3_id=c3.id \n" +
            "LEFT JOIN base_category2 c2 ON c3.category2_id=c2.id \n" +
            "LEFT JOIN base_category1 c1 ON c2.category1_id=c1.id \n" +
            "GROUP BY c1.name ORDER BY `value` desc")
    List<AA> getlist3();

    @Select("SELECT * FROM \n" +
            "(SELECT count(*) 'newNum'FROM user_info WHERE TIMESTAMPDIFF(month,create_time,NOW())<1)a,\n" +
            "(SELECT count(*) 'oldNum'FROM user_info WHERE TIMESTAMPDIFF(month,create_time,NOW())>1)b")
    List<AB> getlist4();

    @Select("SELECT c2.name,sum(o.final_total_amount) `value`FROM order_info o \n" +
            "LEFT JOIN order_detail d ON d.order_id=o.id \n" +
            "LEFT JOIN sku_info s ON d.sku_id=s.id \n" +
            "LEFT JOIN base_category3 c3 ON s.category3_id=c3.id \n" +
            "LEFT JOIN base_category2 c2 ON c3.category2_id=c2.id\n" +
            "GROUP BY c2.name ORDER BY `value` desc")
    List<AA> getlist5();

    @Select("SELECT u.name,sum(o.final_total_amount)`value`FROM order_info o \n" +
            "LEFT JOIN user_info u ON o.user_id=u.id \n" +
            "GROUP BY u.birthday ORDER BY `value` desc")
    List<AA> getlist6();

    @Select("SELECT yys name,sum(final_total_amount) `value` FROM order_info WHERE yys is not null GROUP BY\n" +
            " name ORDER BY `value` desc")
    List<AA> getlist7();

    @Select("SELECT b.name,\n" +
            "if(u.gender,'男','女') sex,\n" +
            "sum(o.final_total_amount) `value`FROM user_info u \n" +
            "LEFT JOIN order_info o ON o.user_id=u.id \n" +
            "LEFT JOIN order_detail d ON d.order_id=o.id \n" +
            "LEFT JOIN sku_info s ON d.sku_id=s.id \n" +
            "LEFT JOIN base_trademark b on s.tm_id=b.id \n" +
            "WHERE b.name is not null \n" +
            "GROUP BY b.name ORDER BY `value` desc  ")
    List<AA> getlist8();
    @Select("CALL lp()")
    double getlist9();

    @Select("SELECT p.spu_name,sum(o.final_total_amount) `value`FROM order_info o \n" +
            "LEFT JOIN order_detail d ON d.order_id=o.id \n" +
            "LEFT JOIN sku_info s ON d.sku_id=s.id \n" +
            "LEFT JOIN spu_info p ON s.spu_id=p.id \n" +
            "GROUP BY p.spu_name ORDER BY `value` desc")
    List<AC> getlist10();
}
